package org.sevaapp.dao;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.*;
import java.sql.*;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.sevaapp.domain.Applicant;

public class ApplicantJDBCImpl {
	private static final Log log = LogFactory.getLog(ApplicantJDBCImpl.class);
	
	Connection conn;
	public void getConnection()
	  {
		try
	    {
			Properties props = new Properties();   
	        //FileInputStream in = new FileInputStream("jdbc.properties");
			InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");  
	        props.load(in);   
	        in.close();   
	        log.info("Loaded properties succesfully");
	        
	        String drivers = props.getProperty("jdbc.driverClassName");   
	        if (drivers != null)   
	        {   
//	            System.setProperty("jdbc.driverClassName", drivers);   
	            Class.forName(drivers).newInstance();
	        }   
	           
	        String url = props.getProperty("jdbc.url");   
	        String username = props.getProperty("jdbc.username");   
	        String password = props.getProperty("jdbc.password");   
	           
	        conn = DriverManager.getConnection(url, username, password);   
	    
//	      Class.forName("org.hsqldb.jdbcDriver").newInstance();
//	      String url = "jdbc:hsqldb:mem:SEVAHC";
//	      conn = DriverManager.getConnection(url, "sa", "");
//	      String url = "jdbc:mysql://localhost:3306/miseva?autoReconnect=true";
//	      conn = DriverManager.getConnection(url, "root", "");
//	      String url = "jdbc:mysql://localhost:3307/miseva?autoReconnect=true";
//	      conn = DriverManager.getConnection(url, "miseva", "HinduTemple");
	    }
		catch (ClassNotFoundException ex ){log.debug(ex.getMessage());}
		catch (InstantiationException ex ){log.debug(ex.getMessage());}
		catch (IllegalAccessException ex ) {log.debug(ex.getMessage());}
	    catch (FileNotFoundException ex)          {log.debug(ex.getMessage());}
	    catch (SQLException ex)           {log.debug(ex.getMessage());}
	    catch (IOException ex)           {log.debug(ex.getMessage());}
	  }
	
	public Applicant selectApplicant(String loginId)
	  {
		Applicant appl = new Applicant(); 
		
		getConnection();
	    //System.out.println("[OUTPUT FROM SELECT]");
	    String query = "SELECT id," +
	    		"address1," +
	    		"address2," +
	    		"cellPhone," +
	    		"city," +
	    		"comments," +
	    		"document1," +
	    		"document2," +
	    		"document3," +
	    		"document4," +
	    		"document5," +
	    		"document6," +
	    		"email," +
	    		"evaluatorName," +
	    		"firstName," +
	    		"homePhone," +
	    		"lastDocDate," +
	    		"lastName," +
	    		"loginId," +
	    		"middleName," +
	    		"state," +
	    		"status," +
	    		"statusDate," +
	    		"zip " +
	    		"FROM applicant WHERE loginId = '" + loginId +"'";
	    
	    try
	    {
	      Statement st = conn.createStatement();
	      ResultSet rs = st.executeQuery(query);
	      while (rs.next())
	      {
	        appl.setId(rs.getInt("id"));
	        appl.setAddress1(rs.getString("address1"));
	        appl.setAddress2(rs.getString("address2"));
	        appl.setCellPhone(rs.getString("cellPhone"));
	        appl.setCity(rs.getString("city"));
	        appl.setComments(rs.getString("comments"));
	        appl.setDocument1(rs.getString("document1"));
	        appl.setDocument2(rs.getString("document2"));
	        appl.setDocument3(rs.getString("document3"));
	        appl.setDocument4(rs.getString("document4"));
	        appl.setDocument5(rs.getString("document5"));
	        appl.setDocument6(rs.getString("document6"));
	        appl.setEmail(rs.getString("email"));
	        appl.setEvaluatorName(rs.getString("evaluatorName"));
	        appl.setFirstName(rs.getString("firstName"));
	        appl.setHomePhone(rs.getString("homePhone"));
	        appl.setLastDocDate(rs.getDate("lastDocDate"));
	        appl.setLastName(rs.getString("lastName"));
	        appl.setLoginId(rs.getString("loginId"));
	        appl.setMiddleName(rs.getString("middleName"));
	        appl.setState(rs.getString("state"));
	        appl.setStatus(rs.getString("state"));
	        appl.setStatusDate(rs.getDate("statusDate"));
	        appl.setZip(rs.getInt("zip"));
	        
	      }
	      
//	      return appl;
	    }
	    catch (SQLException ex)
	    {
	      log.debug(ex.getMessage());
	    }
	    finally
	    {
	    	try{
	    	 conn.close();
	    	}catch(SQLException ex){
	    	 log.debug(ex.getMessage());
	    	}
	    }
	    return appl;
	  }


}
